BigQuery で SQL を実行する時に便利なオプション７選

#Google BigQuery

#Google Cloud (GCP)

みかみ

2021.06.18

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは、みかみです。

やりたいこと

他のデータベースと BigQuery の SQL 実行に違いがあるのか知りたい
BigQuery ならではの SQL 実行オプションを知りたい

前提

本エントリでは、bq query コマンドのオプションを指定して SQL を実行します。

BigQuery で SQL を実行するには、他にも管理コンソールのクエリエディタやクライアントライブラリなども利用できるので、オプションの指定方法はそれぞれのインターフェースに合わせてご確認ください。

クエリの実行｜ BigQuery ドキュメント

ドライラン

BigQuery のオンデマンド料金では、SQL 実行時にスキャンデータ量に応じて料金が発生します。

オンデマンド分析の料金｜ BigQuery ドキュメント

SQL 実行時に --dry_run オプションを指定すると、スキャンデータ量を確認することができます。

mikami_yuki@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --dry_run \
> 'SELECT name from dataset_1.dogs'
Query successfully validated. Assuming the tables are not modified, running this query will process 128 bytes of data.

SQL 実行前に --dry_run でデータ量を確認することで、LIMIT 句で返却レコード数を制限してもスキャンデータ量が変わらないことや、複数テーブルを JOIN する SQL で意図せず膨大なスキャンデータ量が課金対象にならないか確認することができます。

最大スキャンデータ量制限

SQL 実行時に想定以上の課金が発生しないように、スキャン可能なデータ量を制限することができます。

--maximum_bytes_billed オプションで最大データ量を指定しておくと、スキャンデータ量が指定を超える場合は SQL がエラーとなり、課金が発生しません。

mikami_yuki@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --maximum_bytes_billed=100 \
> 'SELECT name from dataset_1.dogs'
Waiting on bqjob_r352d69e5c060560b_0000017a19541df6_1 ... (0s) Current status: DONE
BigQuery error in query operation: Error processing job 'cm-da-mikami-yuki-258308:bqjob_r352d69e5c060560b_0000017a19541df6_1': Query exceeded limit for bytes billed: 100. 10485760
or higher required.

スキャンデータ量制限はプロジェクト単位で設定することもできますが、SQL 単位で個別に指定することも可能です。

開始行、結果行数指定

--start_row や --max_rows オプションで、SQL が返す最初の行や最大行数を指定することができます。

オプション指定なしの場合の SQL 実行結果が以下です。

mikami_yuki@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query \
> 'SELECT id, name from dataset_1.dogs ORDER BY id'
Waiting on bqjob_r73a33a82f4ad43ce_0000017a19744652_1 ... (0s) Current status: DONE
+----+------------------------+
| id |          name          |
+----+------------------------+
|  1 | シェパード             |
|  2 | シベリアンハスキー     |
|  3 | 秋田犬                 |
|  4 | ラブラドールレトリバー |
|  5 | ボルゾイ               |
|  6 | 柴犬                   |
|  7 | コーギー               |
+----+------------------------+

--start_row に 0オリジンで開始行を指定すると、開始行以降の結果が取得できます。

mikami_yuki@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --start_row=4 \
> 'SELECT id, name from dataset_1.dogs ORDER BY id'
Waiting on bqjob_r13d4de0a7dce8eb7_0000017a1975bd04_1 ... (0s) Current status: DONE
+----+----------+
| id |   name   |
+----+----------+
|  5 | ボルゾイ |
|  6 | 柴犬     |
|  7 | コーギー |
+----+----------+

また、--max_rows で取得行数を指定すると、指定した行数だけを絞って取得することができます。

ikami_yuki@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --max_rows=3 \
> 'SELECT id, name from dataset_1.dogs ORDER BY id'
Waiting on bqjob_r5f0e1c2ead8b0c60_0000017a19786199_1 ... (0s) Current status: DONE
+----+--------------------+
| id |        name        |
+----+--------------------+
|  1 | シェパード         |
|  2 | シベリアンハスキー |
|  3 | 秋田犬             |
+----+--------------------+

ただし、LIMIT 句の場合同様、行数を限定しても SQL のスキャンデータ量（＝クエリ課金されるデータ量）は変わらないのでご注意ください。

宛先テーブル指定

--destination_table オプションで、SELECT 結果を別テーブルに保存することができます。宛先に指定するテーブルはあらかじめ作成しておく必要はありません。

mikami_yuki@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --destination_table=dataset_1.result_dogs \
> 'SELECT name from dataset_1.dogs ORDER BY id'
Waiting on bqjob_r30cd2bf10bb5aac_0000017a198341d0_1 ... (1s) Current status: DONE
+------------------------+
|          name          |
+------------------------+
| シェパード             |
| シベリアンハスキー     |
| 秋田犬                 |
| ラブラドールレトリバー |
| ボルゾイ               |
| 柴犬                   |
| コーギー               |
+------------------------+

CREATE TABLE ... AS 構文と同様の処理が実行できるわけですが、 --append_table=true や --replace=true を一緒に指定することで、宛先テーブルが既に存在している場合でも追記や洗い替えでデータを格納することができます。

mikami_yuki@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --destination_table=dataset_1.result_dogs --append_table=true \
> 'SELECT name from dataset_1.dogs ORDER BY id'
Waiting on bqjob_r35efb71bd11803a_0000017a19893736_1 ... (0s) Current status: DONE
+------------------------+
|          name          |
+------------------------+
| シェパード             |
| シベリアンハスキー     |
| 秋田犬                 |
| ラブラドールレトリバー |
| ボルゾイ               |
| 柴犬                   |
| コーギー               |
| シェパード             |
| シベリアンハスキー     |
| 秋田犬                 |
| ラブラドールレトリバー |
| ボルゾイ               |
| 柴犬                   |
| コーギー               |
+------------------------+

mikami_yuki@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --destination_table=dataset_1.result_dogs --replace=true \
> 'SELECT name from dataset_1.dogs ORDER BY id'
Waiting on bqjob_r55e65a223950d832_0000017a198eadb0_1 ... (0s) Current status: DONE
+------------------------+
|          name          |
+------------------------+
| シェパード             |
| シベリアンハスキー     |
| 秋田犬                 |
| ラブラドールレトリバー |
| ボルゾイ               |
| 柴犬                   |
| コーギー               |
+------------------------+

なお、宛先テーブルが既にある場合に --append_table や --replace を指定しない場合、または false で指定した場合には、SQL はエラーになります。

mikami_yuki@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --destination_table=dataset_1.result_dogs --append_table=false \
> 'SELECT name from dataset_1.dogs ORDER BY id'
BigQuery error in query operation: Error processing job 'cm-da-mikami-yuki-258308:bqjob_r766782643abaa7b9_0000017a198acb6f_1': Already Exists: Table cm-da-mikami-
yuki-258308:dataset_1.result_dogs

ラベル付与

SQL 実行後に実行時間や slot 使用率などを確認したい場合、ラベルを付与しておくと確認対象の SQL を判別するのに便利です。

mikami_yuki@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --label=name:label_sample \
> 'SELECT id, name from dataset_1.dogs WHERE id = 5'
Waiting on bqjob_r4bc87f20d6607946_0000017a19a4d7a8_1 ... (0s) Current status: DONE
+----+----------+
| id |   name   |
+----+----------+
|  5 | ボルゾイ |
+----+----------+

SQL の実行時間や slot 利用率は、INFORMATION_SCHEMA や bq コマンドなどで確認可能です。

以下のエントリでも SQL のラベルについてご紹介しているので、ぜひ合わせてご参照ください。

BigQueryのジョブのラベル機能が便利でした（小ネタ）｜ DevelopersIO

バッチ実行（SQL 優先度指定）

BigQuery では、SQL を以下２種類の方式で実行することができます。

インタラクティブクエリ
バッチクエリ

通常はインタラクティブクエリで即時実行されますが、--batch オプションでバッチクエリを指定すれば、BigQuery のリソース状態に応じて非同期（優先度低）で実行することができます。

mikami_yuki@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --batch=true \
> 'SELECT id, name from dataset_1.dogs WHERE id = 1'
Waiting on bqjob_r55a258ebbdb0ac2c_0000017a19d8f045_1 ... (0s) Current status: DONE
+----+------------+
| id |    name    |
+----+------------+
|  1 | シェパード |
+----+------------+

もともとフェアスケジューリングで並列実行には強い BigQuery ですが、例えば午前中はシステム処理が走っているのでアドホッククエリの実行は控えたい場合など、バッチクエリを指定しておけば、システム処理のじゃますることなく時間を気にせず SQL を実行することができます。

BigQuery のフェアスケジューリング｜ BigQuery ドキュメント

スケジューリング

BigQuery では SQL のスケジュール実行を設定することができます。

クエリのスケジューリング｜ BigQuery ドキュメント

例えば、毎日決まった時間にデータを集計して別テーブルに格納したい場合など、スケジュール実行で SQL を自動実行するように指定することが可能です。

mikami_yuki@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --schedule='every day 12:30' \
> --display_name='Option Sample Schedule' \
> --destination_table=dataset_1.result_dogs \
> --replace=true \
> --use_legacy_sql=false \
> 'SELECT name from dataset_1.dogs ORDER BY id'
Transfer configuration 'projects/797147019523/locations/asia-northeast1/transferConfigs/60d85d79-0000-2cba-be01-089e082256f0' successfully created.